Course: Android App Development
Topic: Understanding SQLite Database Fundamentals
Course Link: Android App Development Course
Demo Link: Register for a Free Course Demo
Website: JustAcademy
SQLite is a lightweight, embedded relational database management system used to store and manage structured data directly within an application. In Android development, SQLite allows applications to save information locally on a device without requiring a separate database server or an internet connection.
Applications often need to store information such as user profiles, product details, notes, shopping cart items, transaction records, and task lists. SQLite provides a reliable way to save this information so it remains available even after the application is closed or the device is restarted.
SQLite is integrated into the Android platform and supports SQL (Structured Query Language) for creating tables, inserting records, retrieving data, updating existing records, and deleting unwanted information.
Consider a notes application. When a user writes a note and closes the application, the note should remain available when the application is opened again. SQLite can store the note's title, content, and creation date in a local database.
SQLite is a self-contained, serverless, zero-configuration, transactional relational database engine. Unlike databases that require a dedicated server process, SQLite works directly with a database file stored on the device.
SQLite is useful when an Android application needs persistent local storage. It can support offline functionality, improve data availability, and reduce the need to request information repeatedly from a remote server.
| Use Case | Description |
|---|---|
| Notes application | Stores note titles, descriptions, and timestamps. |
| Task manager | Stores tasks, completion status, and due dates. |
| Expense tracker | Maintains expenses, amounts, categories, and dates. |
| Offline application | Allows users to access previously saved data without internet access. |
| Inventory management | Stores product names, quantities, and prices locally. |
| Cached information | Stores selected data retrieved from a remote service for later access. |
SQLite is a relational database system. It organizes information into tables consisting of rows and columns.
A database is an organized collection of related information. An Android application may maintain a database named StudentDB containing student records.
A table stores records of a particular type. For example, a students table may contain student identification numbers, names, email addresses, and ages.
A row represents one individual entry in a table.
A column represents a specific attribute of each record, such as a student's name or email address.
A primary key uniquely identifies each record in a table. An integer column declared as INTEGER PRIMARY KEY can serve as the record identifier.
A foreign key establishes a relationship between tables by referencing a key in another table. Foreign key enforcement should be enabled when referential integrity is required.
| student_id | name | age | |
|---|---|---|---|
| 1 | Rahul | [email protected] | 21 |
| 2 | Priya | [email protected] | 22 |
| 3 | Amit | [email protected] | 20 |
In this example, student_id identifies each student, while name, email, and age store additional information.
SQLite uses a flexible type system. Each value has a storage class, and a column can generally store values of different storage classes unless additional constraints or stricter table definitions are used.
| Storage Class | Purpose | Example |
|---|---|---|
| NULL | Represents a missing or unknown value. | NULL |
| INTEGER | Stores whole numbers. | 25 |
| REAL | Stores floating-point numbers. | 99.95 |
| TEXT | Stores text strings. | 'Android' |
| BLOB | Stores binary data. | Image or file bytes |
INTEGER for identifiers and whole-number quantities.TEXT for names, addresses, and descriptions.REAL for decimal measurements.BLOB for binary content when storing it directly is appropriate.NUMERIC for values that may be stored using an appropriate numeric representation.Choose column declarations that express the intended data and add constraints to protect data quality. For currency, consider storing values as integer minor units, such as paise, when exact arithmetic is important.
SQL is the language used to communicate with a relational database. SQLite supports commands for defining tables, managing records, querying data, and controlling transactions.
DDL commands define or modify database structures.
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER
);This statement creates a table named students with four columns. The name is required, and each non-null email must be unique.
DML commands insert, update, and delete records.
INSERT INTO students (name, email, age)
VALUES ('Rahul', '[email protected]', 21);
UPDATE students
SET age = 22
WHERE id = 1;
DELETE FROM students
WHERE id = 1;The WHERE condition is important for targeted updates and deletions. Without it, an update can modify every row, while a delete can remove every row from the table.
The SELECT statement retrieves records from a table.
SELECT * FROM students;
SELECT name, age
FROM students
WHERE age >= 21
ORDER BY name ASC;The second query returns the names and ages of students aged 21 or older, ordered alphabetically by name.
Android provides the SQLiteOpenHelper class to help developers create, open, and upgrade SQLite databases. The helper manages database initialization and invokes lifecycle callbacks when appropriate.
| Method | Purpose |
|---|---|
onCreate() |
Creates the initial database schema when the database is first created. |
onUpgrade() |
Handles schema upgrades when the database version increases. |
getWritableDatabase() |
Opens or creates a database for reading and writing. |
getReadableDatabase() |
Opens a database for reading, potentially falling back to a read-only database when necessary. |
close() |
Closes the database helper's database connection when it is no longer needed. |
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
class StudentDatabaseHelper(context: Context) :
SQLiteOpenHelper(
context,
"StudentDB",
null,
1
) {
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(
"""
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER NOT NULL
)
""".trimIndent()
)
}
override fun onUpgrade(
db: SQLiteDatabase,
oldVersion: Int,
newVersion: Int
) {
// Add migration logic when the schema changes.
}
}Explanation:
StudentDatabaseHelper extends SQLiteOpenHelper.StudentDB is the database filename.1.onCreate() creates the students table for a new database.AUTOINCREMENT requests automatically generated, increasing identifiers for inserted rows.onUpgrade() is the place to implement migrations for newer database versions.This is a basic demonstration. Production applications should use deliberate migration logic that preserves existing data rather than dropping and recreating tables without a recovery plan.
Inserting data means adding a new record to a database table. Android offers the ContentValues class to represent column-value pairs for insert operations.
import android.content.ContentValues
val dbHelper = StudentDatabaseHelper(context)
val db = dbHelper.writableDatabase
val values = ContentValues().apply {
put("name", "Rahul")
put("email", "[email protected]")
put("age", 21)
}
val insertedId = db.insert(
"students",
null,
values
)
if (insertedId != -1L) {
println("Student inserted successfully")
} else {
println("Insert failed")
}The insert() method returns the new row ID on success or -1 if the insertion fails. The example assumes that a valid Android Context is available and that database operations run away from the main thread.
Retrieving data means reading saved records from the database. Android's Cursor class allows an application to move through the rows returned by a query and read individual column values.
val db = dbHelper.readableDatabase
db.query(
"students",
arrayOf("id", "name", "email", "age"),
null,
null,
null,
null,
"name ASC"
).use { cursor ->
val idIndex = cursor.getColumnIndexOrThrow("id")
val nameIndex = cursor.getColumnIndexOrThrow("name")
val emailIndex = cursor.getColumnIndexOrThrow("email")
val ageIndex = cursor.getColumnIndexOrThrow("age")
while (cursor.moveToNext()) {
val id = cursor.getLong(idIndex)
val name = cursor.getString(nameIndex)
val email = cursor.getString(emailIndex)
val age = cursor.getInt(ageIndex)
println("$id: $name, $email, $age")
}
}The query() method builds a structured query. The use block closes the cursor automatically after processing, helping prevent resource leaks.
db.query(
"students",
arrayOf("id", "name", "age"),
"age >= ?",
arrayOf("21"),
null,
null,
"age DESC"
)The selection argument age >= ? uses a placeholder. The actual value is supplied separately, helping prevent SQL injection and avoiding unsafe string concatenation.
An update modifies existing records without creating a new record. Android's update() method accepts the table name, updated values, a selection condition, and selection arguments.
val values = ContentValues().apply {
put("age", 23)
}
val rowsUpdated = db.update(
"students",
values,
"id = ?",
arrayOf("1")
)
println("Rows updated: $rowsUpdated")The method returns the number of rows affected. Always use an appropriate selection condition when changing a particular record.
Deleting data removes records from a table. The delete() method can target particular records through a selection condition.
val rowsDeleted = db.delete(
"students",
"id = ?",
arrayOf("1")
)
println("Rows deleted: $rowsDeleted")This operation removes the student record with ID 1. Deleting a record is different from deleting the entire database file or dropping a table.
A transaction groups multiple database operations into one logical unit. Transactions are useful when several changes must either succeed together or be rolled back together.
SQLite transactions help maintain the ACID properties:
db.beginTransaction()
try {
val first = ContentValues().apply {
put("name", "Neha")
put("email", "[email protected]")
put("age", 24)
}
val firstId = db.insert("students", null, first)
check(firstId != -1L) { "First insert failed" }
val second = ContentValues().apply {
put("name", "Arjun")
put("email", "[email protected]")
put("age", 25)
}
val secondId = db.insert("students", null, second)
check(secondId != -1L) { "Second insert failed" }
db.setTransactionSuccessful()
} finally {
db.endTransaction()
}If an exception occurs before setTransactionSuccessful(), the transaction is not marked successful and its changes are rolled back when it ends. The finally block ensures that the transaction is ended.
A database schema defines the structure of tables, columns, indexes, constraints, and relationships. As an application evolves, the database schema may need to change.
For example, version 1 of a student database might contain a name and email address. Version 2 might add a phone number. The application needs a migration that adds the new column without destroying existing student records.
override fun onUpgrade(
db: SQLiteDatabase,
oldVersion: Int,
newVersion: Int
) {
if (oldVersion < 2) {
db.execSQL(
"ALTER TABLE students ADD COLUMN phone TEXT"
)
}
}When the helper's database version is increased to 2, the migration adds the phone column to an existing version 1 database. Real applications should support every required upgrade path, including upgrades that skip intermediate versions.
Constraints protect the integrity of stored information by enforcing rules at the database level.
| Constraint | Purpose | Example |
|---|---|---|
| PRIMARY KEY | Uniquely identifies records. | id INTEGER PRIMARY KEY |
| NOT NULL | Prevents a column from containing NULL. | name TEXT NOT NULL |
| UNIQUE | Prevents duplicate non-null values in a constrained column. | email TEXT UNIQUE |
| CHECK | Enforces a condition on a value. | age INTEGER CHECK(age >= 0) |
| FOREIGN KEY | Enforces a relationship with another table when enabled. | department_id INTEGER REFERENCES departments(id) |
Database constraints are important because application-level validation alone may not prevent every invalid record, especially when multiple code paths write to the same database.
An index is a data structure that helps SQLite locate records more efficiently for certain queries. Indexes can improve searches and sorting but consume storage and may slow down inserts, updates, and deletes because the indexes must also be maintained.
CREATE INDEX idx_students_name
ON students(name);
SELECT *
FROM students
WHERE name = 'Rahul';The index may improve queries that filter or sort by the indexed name column. Whether it actually improves performance depends on the query and the data distribution. Use EXPLAIN QUERY PLAN to inspect how SQLite executes a query.
use where applicable.Android developers can work with SQLite directly or use Room, an abstraction layer built on top of SQLite. Both approaches support local relational data storage, but they differ in how much database plumbing the developer manages.
| Feature | SQLiteOpenHelper | Room |
|---|---|---|
| Database access | Uses SQLite APIs and SQL directly. | Uses entities, DAOs, and Room database classes. |
| Boilerplate | More manual code for queries and cursors. | Reduces repetitive database access code. |
| Query validation | SQL is generally checked when executed. | Room validates many SQL queries during compilation. |
| Schema migrations | Implemented manually using helper callbacks. | Uses explicit migration mechanisms and schema support. |
| Best suited for | Learning SQLite fundamentals or working directly with SQLite APIs. | Many modern Android applications that need maintainable local persistence. |
Room is generally recommended for new Android applications that need a structured database layer. Understanding SQLite remains valuable because Room uses SQLite underneath and developers still need to understand tables, queries, relationships, indexes, and migrations.
| Problem | Possible Cause | Solution |
|---|---|---|
| Table does not exist | The table was not created or the SQL uses the wrong table name. | Check onCreate(), table names, and database initialization. |
| Duplicate value error | A UNIQUE constraint was violated. | Validate duplicate values or use an appropriate conflict-handling strategy. |
| Column not found | The query references a missing or incorrectly named column. | Verify the schema and update migrations. |
| Data is missing | The query filters out the record, the insert failed, or a different database is being opened. | Check return values, selection arguments, database names, and transaction results. |
| Slow queries | Large scans, inefficient queries, or excessive work on the main thread. | Inspect the query plan, review indexes, and move work off the main thread. |
| Database locked or busy | Concurrent access or long-running transactions. | Shorten transactions and manage database access appropriately. |
| Upgrade failure | Incorrect migration logic or an unsupported version path. | Test migrations from each supported older schema version. |
Build a simple Android application that stores and manages student records using SQLite.
StudentDB.students table with an ID, name, email, and age.The completed project should let users create, view, update, and delete student records while keeping saved data available between application sessions.
products with product ID, name, price, and quantity.onCreate() and onUpgrade().Answer: SQLite is a lightweight, embedded relational database engine that stores structured data in a local database file and supports SQL operations.
Answer: It enables local persistent storage, offline access, and efficient management of structured information without requiring a separate database server.
Answer: It is an Android helper class that manages database creation, opening, and version-based schema upgrades through lifecycle callbacks.
Answer: A primary key identifies records in its own table. A foreign key references a key in another table and helps maintain relationships between records.
Answer: A Cursor provides access to rows returned by a database query. The application moves through the rows and reads the required column values.
Answer: A transaction groups related database operations so they can be committed together or rolled back if the transaction does not complete successfully.
Answer: Use parameterized queries and selection arguments instead of concatenating untrusted user input into SQL statements.
Answer: An index helps the database locate records more efficiently for suitable queries, although it requires additional storage and maintenance.
Answer: Increase the database version and implement migrations that transform existing tables and preserve stored information.
Answer: SQLite is the underlying database engine. Room is an Android persistence library that provides a structured abstraction over SQLite with entities, DAOs, and compile-time query validation.
CREATE TABLE defines a table.INSERT adds records.SELECT retrieves records.UPDATE modifies records.DELETE removes records.SQLiteOpenHelper manages database creation and upgrades.Cursor provides access to query results.Understanding SQLite database fundamentals is an important step in Android application development. SQLite enables developers to store structured information locally, execute SQL queries, manage records, enforce constraints, and maintain data between application sessions.
By learning database creation, CRUD operations, transactions, schema migrations, indexing, and security best practices, developers can build reliable Android applications with persistent offline data. These fundamentals also provide a strong foundation for using Room in modern Android projects.
Explore the Android App Development course to strengthen your understanding of Android components, local data storage, database management, and practical application development.
